package com.tsd.core.utils;

import com.tsd.core.vo.ColumnClass;
import com.tsd.core.vo.HlpException;
import com.tsd.core.vo.TableClass;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;

import java.sql.*;
import java.util.*;

/**
 * 直接通过JDBC操作的工具类
 */
public class JdbcUtil {

    /**
     * 解析JDBC连接
     *
     * @param jdbcUrl JDBC连接
     * @return
     */
    public static Map<String, String> parseJdbcUrl(String jdbcUrl) {
        Map<String, String> map = new HashMap<>();
        String[] split = jdbcUrl.split(":");
        map.put("host", split[2].replace("//", ""));
        String[] portSplit = split[3].split("/");
        map.put("port", portSplit[0]);
        String[] dbSplit = portSplit[1].split("\\?");
        map.put("catalog", dbSplit[0]);
        return map;
    }

    /**
     * 根据参数获取JDBC连接
     *
     * @param driver
     * @param url
     * @param user
     * @param password
     * @return
     * @throws Exception
     */
    public static Connection getConnection(String driver, String url, String user, String password) throws Exception {
        Class.forName(driver);
        Properties props = new Properties();
        props.setProperty("user", user);
        props.setProperty("password", password);
        //Mysql，设置可以获取remarks信息
        props.setProperty("remarks", "true");
        //Mysql, 设置可以获取tables remarks信息
        props.setProperty("useInformationSchema", "true");
        return DriverManager.getConnection(url, props);
    }

    /**
     * 释放JDBC连接
     *
     * @param connection
     */
    public static void releaseConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            connection = null;
        }
    }

    /**
     * 获取数据库列表
     *
     * @param connection
     * @param catalog
     * @return
     * @throws Exception
     */
    public static List<String> getSchemas(Connection connection, String catalog) throws Exception {
        ResultSet rs = null;
        List<String> columnClassList = new ArrayList<>();
        String sql = "SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`";
        if (!HlpUtils.isEmpty(catalog)) {
            sql += " WHERE `SCHEMA_NAME`= ?";
        }
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            if (!HlpUtils.isEmpty(catalog)) {
                statement.setString(1, catalog);
            }
            rs = statement.executeQuery();
            while (rs.next()) {
                String schemaName = rs.getString("SCHEMA_NAME");
                columnClassList.add(schemaName);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
        return columnClassList;
    }

    /**
     * 获取指定库和表(视图)
     *
     * @param connection
     * @param catalog
     * @param tableName
     * @return
     * @throws Exception
     */
    public static List<TableClass> getTables(Connection connection, String catalog, String tableName) throws Exception {
        List<TableClass> tableNames = new ArrayList<TableClass>();
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        ResultSet rs = null;
        try {
            rs = databaseMetaData.getTables(catalog, null, tableName, new String[]{"TABLE", "VIEW"});
            while (rs.next()) {
                TableClass tableClass = new TableClass();
                tableClass.setTableName(rs.getString("TABLE_NAME"));
                tableClass.setTableType(rs.getString("TABLE_TYPE"));
                ResultSet resultSetPK = databaseMetaData.getPrimaryKeys(catalog, "dbo", tableClass.getTableName());
                if (resultSetPK.next()) {
                    tableClass.setPrimaryKey(resultSetPK.getString("TABLE_NAME"));
                }
                String remarks = rs.getString("REMARKS");
                if (StringUtil.isEmpty(remarks)) {
                    tableClass.setTableKind(tableClass.getTableName());
                    tableClass.setTableComment(tableClass.getTableName());
                } else {
                    int pos = remarks.indexOf("-");
                    if (pos > 0) {
                        String kind = remarks.substring(0, pos);
                        tableClass.setTableKind(kind);
                    }
                    tableClass.setTableComment(remarks);
                }
                tableNames.add(tableClass);
            }
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        }
        return tableNames;
    }

    /**
     * 获取指定表|视图的列定义
     *
     * @param connection
     * @param catalog
     * @param tableName
     * @return
     * @throws Exception
     */
    public static List<ColumnClass> getColumns(Connection connection, String catalog, String tableName) throws Exception {
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        ResultSet rs = null;
        List<ColumnClass> columnClassList = new ArrayList<>();
        try {
            rs = databaseMetaData.getColumns(catalog, null, tableName, null);
            while (rs.next()) {
                if (tableName == null) {
                    tableName = rs.getString("TABLE_NAME");
                }
                String columnName = rs.getString("COLUMN_NAME");
                ColumnClass columnClass = new ColumnClass();
                columnClass.setTableName(tableName);
                // 获取字段名称
                columnClass.setColumnName(columnName);
                // 获取字段类型
                columnClass.setColumnType(rs.getString("TYPE_NAME"));
                // 转换字段名称，如 sys_name 变成 SysName
                columnClass.setChangeColumnName(rs.getString("COLUMN_NAME"));
                // 字段在数据库的注释
                columnClass.setColumnComment(rs.getString("REMARKS"));
                columnClass.setLength(rs.getInt("COLUMN_SIZE"));
                columnClass.setFraction_len(rs.getInt("DECIMAL_DIGITS"));
                columnClass.setPosition(rs.getInt("ORDINAL_POSITION"));
                columnClassList.add(columnClass);
            }
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        }
        return columnClassList;
    }

    /**
     * 根据sql的结果集获取列信息
     *
     * @param rs
     * @return
     * @throws SQLException
     */
    private static List<ColumnClass> initSqlColumnClass(ResultSet rs) throws SQLException {
        List<ColumnClass> list = new ArrayList<>();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            ColumnClass columnClass = new ColumnClass();
            columnClass.setTableName(md.getTableName(i));
            columnClass.setColumnName(md.getColumnName(i));
            list.add(columnClass);
        }
        return list;
    }

    /**
     * 通过JDBC URL获得Catalog（数据库名称）
     *
     * @param jdbcUrl
     * @return
     * @throws HlpException
     */
    public static String getCatalogFromUrl(String jdbcUrl) throws HlpException {
        int index2 = jdbcUrl.indexOf("?");
        if (index2 > 0) {
            int index1 = jdbcUrl.lastIndexOf("/", index2);
            if (index1 > 0) {
                return jdbcUrl.substring(index1 + 1, index2);
            } else {
                throw new HlpException("jdbcUrl在?之前没有/");
            }
        } else {
            throw new HlpException("jdbcUrl没有包含?");
        }
    }

    /**
     * 执行sql文件，主要用于数据库恢复
     *
     * @param connection
     * @param file_path
     */
    public static void executeSqlFile(Connection connection, String file_path) throws Exception {
        FileSystemResource res = new FileSystemResource(file_path);
        if (res.exists()) {
            EncodedResource resource = new EncodedResource(res, "utf-8");
            ScriptUtils.executeSqlScript(connection, resource);
        } else {
            throw new HlpException("缺少账套创建脚本");
        }
    }

    public static void main(String[] args) {
        try {
            String url = "jdbc:mysql://139.159.161.160:3366/tsd?useUnicode=true&useSSL=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&allowMultiQueries=true";
            String ca = getCatalogFromUrl(url);
            System.out.println(ca);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据connection和sql获取sql结果集的列
     *
     * @param connection
     * @param sql
     * @return
     * @throws SQLException
     */
    public static List<ColumnClass> getSelectSqlColumns(Connection connection, String sql) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        List<ColumnClass> columns = initSqlColumnClass(resultSet);
        return columns;
    }
}
